package com.sinog.biz.schedule;

import cn.hutool.core.collection.CollectionUtil;
import com.sinog.biz.BizApplication;
import com.sinog.biz.config.ThreadPoolConfig;
import com.sinog.biz.mapper.havexml.PrisonerMainMapper;
import com.sinog.biz.mapper.havexml.other.PrisonerTransferMapper;
import com.sinog.biz.util.CodeUrlUtils;
import com.sinog.biz.util.DataSourceCheckUtils;
import com.sinog.biz.util.IdGen;
import com.sinog.biz.util.JdbcUtils;
import com.sinog.core.domain.PrisonerMain;
import com.sinog.core.model.PrisonerTransferVo;
import com.sinog.core.util.JsonUtil;
import com.sinog.core.utilbean.Constants;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Semaphore;

/**
 * @author lzq
 * @desc 基础数据同步（由原来的http接口方式省级到中间库方式），跨省同步，各省从司法部中间库拉取本省数据 任务1： 任务2：任务3： 每分钟执行一次
 * @date 2020-10-19 9:57 上午
 */
@Slf4j
@Component
@Configuration
@ConditionalOnProperty(name = "gkzx.azbj.dataSync.open", havingValue = "true")
public class MiddleSchedule {

    @Resource
    private PrisonerTransferMapper prisonerTransferMapper;
    @Resource
    private PrisonerMainMapper prisonerMainMapper;
    private JdbcTemplate middleJdbcTemplate;
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ThreadPoolConfig threadPoolConfig;

    /**
     * 任务1：从司法部中间库拉取外省推送的本省数据
     */
   @Scheduled(cron = "${gkzx.azbj.dataSync.cron}")
    private void getLocalProvinceDataFromMiddle() {
        log.error(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>getLocalProvinceDataFromMiddle>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
        String localCode = CodeUrlUtils.getLocalCode();
        String sql = "select * from middle where state='2' and in_province_code='" + localCode + "' order by order_num limit 300";
        String reason;
        String sql1;
        ApplicationContext applicationContext = BizApplication.applicationContext;
        if (!ObjectUtils.allNotNull(middleJdbcTemplate)) {
            middleJdbcTemplate = (JdbcTemplate) applicationContext.getBean("middleJdbcTemplate");
            jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        }
        //检测全部数据源是否可用
        boolean allRunning = DataSourceCheckUtils.checkRunningStatus(middleJdbcTemplate, jdbcTemplate);
        if (true) {
            List<Map<String, Object>> lists;
            try {
                lists = middleJdbcTemplate.queryForList(sql);
            } catch (Exception e) {
                log.error("中间库连接失败{}", e.getMessage());
                return;
            }
            log.error(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>同步中间库查询出记录:{}条", lists.size());
            List<String> sqls = new ArrayList<>();
            String[] str3 = new String[lists.size()];
            for (Map<String, Object> map : lists) {
                String statement = (String) map.get("statement");
                String id = (String) map.get("id");
                try {
                    jdbcTemplate.update(statement);
                    sql1 = "update middle set state='1' where id='" + id + "'";
                    log.error(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>执行成功,id:{}", id);
                } catch (Exception e) {
                    log.error(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>执行错误,id:{},e:{}", id,e.getMessage());
                    reason = "失败";
                    sql1 = "update middle set state='3',reason='" + reason + "' where id='" + id + "'";
                }
                sqls.add(sql1);
            }
            str3 = sqls.toArray(str3);
            if (str3.length > 0) {
                log.error(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>开始批量插入:{}", sqls.size());
                middleJdbcTemplate.batchUpdate(str3);
                log.error(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>结束批量插入 ^_^");
            }
        } else {
            log.error("检测到数据源不可用,本次执行定时从司法部抽取同步数据任务失败!");
        }
    }

    /**
     * 任务2：衔接期内,转监任务还未做处理的省份,自动驳回
     */
   @Scheduled(cron = "${gkzx.azbj.dataSync.cron}")
    public void transferSchedule() {
        // 1.查找所有转监任务(未完成转监工作的记录)
        String localCode = CodeUrlUtils.getLocalCode();
        // 2.处理已经出狱的的转监任务,及时驳回
        ApplicationContext applicationContext = BizApplication.applicationContext;
        if (!ObjectUtils.allNotNull(middleJdbcTemplate)) {
            middleJdbcTemplate = (JdbcTemplate) applicationContext.getBean("middleJdbcTemplate");
        }
        //检测全部数据源是否可用
        boolean allRunning = DataSourceCheckUtils.checkRunningStatus(middleJdbcTemplate);
        if (true) {
            // 3.根据时间查找已经出狱的服刑人员
            List<PrisonerTransferVo> resultVos = prisonerTransferMapper.searchTransferToPrison();
            for (PrisonerTransferVo resultVo : resultVos) {
                String id = resultVo.getId();
                String priNum = resultVo.getPriNum();
                String tranProvince = resultVo.getTranProvince();
                prisonerTransferMapper.updatePrisonerTransferById(id, Constants.NumberStr.NUM_STR_FIVE);
                // 如果是跨省数据
                if (StringUtils.equals(tranProvince, localCode)) {
                    // 将跨省记录删除
                    executeDelSql(priNum, localCode, tranProvince);
                    String prisonerDeleteSql = "delete from prisoner_transfer where id = \'" + id + "\'";
                    String middleSql1 = JdbcUtils.createMiddleSql(priNum, prisonerDeleteSql, Constants.Number.NUM_ZERO,localCode, tranProvince);
                    middleJdbcTemplate.update(middleSql1);
                }
            }
        } else {
            log.error("检测到数据源不可用,本次【衔接期内,转监任务还未做处理的省份,自动驳回】任务失败!");
        }
    }

    /**
     * 任务3：跨省转监的僵尸数据,自动驳回,联系工作人员处理
     */
   @Scheduled(cron = "${gkzx.azbj.dataSync.cron}")
    public void transferZombieData() {
        log.error(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>transferZombieData>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
        String localCode = CodeUrlUtils.getLocalCode();
        ApplicationContext applicationContext = BizApplication.applicationContext;
        if (!ObjectUtils.allNotNull(jdbcTemplate)) {
            jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        }
        //检测全部数据源是否可用
        boolean allRunning = DataSourceCheckUtils.checkRunningStatus(jdbcTemplate);
        if (true) {
            List<PrisonerTransferVo> resultVos = prisonerTransferMapper.searchZombieData();
            for (PrisonerTransferVo resultVo : resultVos) {
                String id = resultVo.getId();
                String priNum = resultVo.getPriNum();
                String tranProvince = resultVo.getTranProvince();
                PrisonerMain prisonerMain = prisonerMainMapper.selectPrisonerMain(priNum);
                String nativepro = "";
                if (null != prisonerMain) {
                    nativepro = prisonerMain.getNativepro();
                }
                // !nativepro.equals(tranProvince) 说明专监跨省,tranProvince.equals(localCode) 说明跨省为当前省份,将本地记录删除
                if (!nativepro.equals(tranProvince) && tranProvince.equals(localCode)) {
                    // 删除跨省记录后,自动驳回转监,将转监状态改为6(mybatis拦截器执行跨省请求)
                    prisonerTransferMapper.updatePrisonerTransferById(id, Constants.NumberStr.NUM_STR_SIX);
                    // 将当前迁移的数据在本地清除
                    String prisonerTransferDeleteSql = "delete from prisoner_transfer where id = '" + id + "'";
                    String prisonerMainDeleteSql = "delete from prisoner_main where pri_num='" + priNum + "'";
                    String prisaveTempDeleteSql = "delete from prisave_temp where Pri_Num='" + priNum + "'";
                    String prisonerFxDeleteSql = "delete from prisoner_fx where pri_num='" + priNum + "'";
                    String prisonerHcDeleteSql = "delete from prisoner_hc where pri_num='" + priNum + "'";
                    String prisonerSrDeleteSql = "delete from prisoner_sr where pri_num='" + priNum + "'";
                    String prisonerZnDeleteSql = "delete from prisoner_zn where pri_num='" + priNum + "'";
                    String prisonerZzDeleteSql = "delete from prisoner_zz where pri_num='" + priNum + "'";
                    String uploadAppendixFileDeleteSql = "delete from upload_appendix_file where pri_num='" + priNum + "'";
                    String prisonersJxjsDeleteSql = "delete from prisoners_jxjs where pri_num='" + priNum + "'";
                    String prisonersZyjnDeleteSql = "delete from prisoners_zyjn where pri_num='" + priNum + "'";
                    String prisonerCjpgDeleteSql = "delete from prisoner_cjpg where pri_num='" + priNum + "'";
                    String prisonerXjxxDeleteSql = "delete from prisoner_xjxx where pri_num='" + priNum + "'";
                    jdbcTemplate.update(prisonerMainDeleteSql);
                    jdbcTemplate.update(prisaveTempDeleteSql);
                    jdbcTemplate.update(prisonerFxDeleteSql);
                    jdbcTemplate.update(prisonerHcDeleteSql);
                    jdbcTemplate.update(prisonerSrDeleteSql);
                    jdbcTemplate.update(prisonerZnDeleteSql);
                    jdbcTemplate.update(prisonerZzDeleteSql);
                    jdbcTemplate.update(uploadAppendixFileDeleteSql);
                    jdbcTemplate.update(prisonersJxjsDeleteSql);
                    jdbcTemplate.update(prisonersZyjnDeleteSql);
                    jdbcTemplate.update(prisonerCjpgDeleteSql);
                    jdbcTemplate.update(prisonerXjxxDeleteSql);
                    jdbcTemplate.update(prisonerTransferDeleteSql);
                }
            }
        } else {
            log.error("检测到数据源不可用,本次【跨省转监的僵尸数据,自动驳回,联系工作人员处理】任务失败!");
        }
    }

    /**
     * 执行删除语句
     *
     * @param priNum    priNum
     * @param localCode localCode
     * @param jzCode    jzCode
     */
    public void executeDelSql(String priNum, String localCode, String jzCode) {
        String prisonerMainDeleteSql = "delete from prisoner_main where pri_num='" + priNum + "'";
        String prisaveTempDeleteSql = "delete from prisave_temp where pri_num='" + priNum + "'";
        String prisonerFxDeleteSql = "delete from prisoner_fx where pri_num='" + priNum + "'";
        String prisonerHcDeleteSql = "delete from prisoner_hc where pri_num='" + priNum + "'";
        String prisonerSrDeleteSql = "delete from prisoner_sr where pri_num='" + priNum + "'";
        String prisonerZnDeleteSql = "delete from prisoner_zn where pri_num='" + priNum + "'";
        String prisonerZzDeleteSql = "delete from prisoner_zz where pri_num='" + priNum + "'";
        String uploadAppendixFileDeleteSql = "delete from upload_appendix_file where pri_num='" + priNum + "'";
        String prisonersJxjsDeleteSql = "delete from prisoners_jxjs where pri_num='" + priNum + "'";
        String prisonersZyjnDeleteSql = "delete from prisoners_zyjn where pri_num='" + priNum + "'";
        String prisonerCjpgDeleteSql = "delete from prisoner_cjpg where Pri_Num='" + priNum + "'";
        String prisonerXjxxDeleteSql = "delete from prisoner_xjxx where pri_num='" + priNum + "'";
        String middleSql1 = JdbcUtils.createMiddleSql(priNum, prisonerMainDeleteSql,Constants.Number.NUM_ZERO, localCode, jzCode);
        middleJdbcTemplate.update(middleSql1);
        String middleSql2 = JdbcUtils.createMiddleSql(priNum, prisaveTempDeleteSql,Constants.Number.NUM_ZERO, localCode, jzCode);
        middleJdbcTemplate.update(middleSql2);
        String middleSql3 = JdbcUtils.createMiddleSql(priNum, prisonerFxDeleteSql,Constants.Number.NUM_ZERO, localCode, jzCode);
        middleJdbcTemplate.update(middleSql3);
        String middleSql4 = JdbcUtils.createMiddleSql(priNum, prisonerHcDeleteSql, Constants.Number.NUM_ZERO,localCode, jzCode);
        middleJdbcTemplate.update(middleSql4);
        String middleSql5 = JdbcUtils.createMiddleSql(priNum, prisonerSrDeleteSql, Constants.Number.NUM_ZERO,localCode, jzCode);
        middleJdbcTemplate.update(middleSql5);
        String middleSql6 = JdbcUtils.createMiddleSql(priNum, prisonerZnDeleteSql, Constants.Number.NUM_ZERO,localCode, jzCode);
        middleJdbcTemplate.update(middleSql6);
        String middleSql7 = JdbcUtils.createMiddleSql(priNum, prisonerZzDeleteSql, Constants.Number.NUM_ZERO,localCode, jzCode);
        middleJdbcTemplate.update(middleSql7);
        String middleSql8 = JdbcUtils.createMiddleSql(priNum, uploadAppendixFileDeleteSql, Constants.Number.NUM_ZERO,localCode, jzCode);
        middleJdbcTemplate.update(middleSql8);
        String middleSql10 = JdbcUtils.createMiddleSql(priNum, prisonersJxjsDeleteSql, Constants.Number.NUM_ZERO,localCode, jzCode);
        middleJdbcTemplate.update(middleSql10);
        String middleSql11 = JdbcUtils.createMiddleSql(priNum, prisonersZyjnDeleteSql,Constants.Number.NUM_ZERO, localCode, jzCode);
        middleJdbcTemplate.update(middleSql11);
        String middleSql12 = JdbcUtils.createMiddleSql(priNum, prisonerCjpgDeleteSql,Constants.Number.NUM_ZERO, localCode, jzCode);
        middleJdbcTemplate.update(middleSql12);
        String middleSql13 = JdbcUtils.createMiddleSql(priNum, prisonerXjxxDeleteSql,Constants.Number.NUM_ZERO, localCode, jzCode);
        middleJdbcTemplate.update(middleSql13);
    }

    public void removeRepeatData() {
        try {
            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>开始执行处理中间库的重复数据..........................");
            //获取middle数据源
            if (middleJdbcTemplate == null) {
                ApplicationContext applicationContext = BizApplication.applicationContext;
                if (!ObjectUtils.allNotNull(middleJdbcTemplate)) {
                    middleJdbcTemplate = (JdbcTemplate) applicationContext.getBean("middleJdbcTemplate");
                }
            }
            Connection connection = middleJdbcTemplate.getDataSource().getConnection();
            Statement connectionStatement = connection.createStatement();
            String priNumSql = "select distinct pri_num as priNum from middle_old group by pri_num";
            List<Map<String, Object>> priNumMap = middleJdbcTemplate.queryForList(priNumSql);
            CountDownLatch countDownLatch = new CountDownLatch(priNumMap.size());
            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>犯罪编号数量：{}",priNumMap.size());
            //正在运行的线程的数量
            int i = 0;
            Semaphore semaphore = new Semaphore(20);
            ThreadPoolTaskExecutor threadPoolTaskExecutor = threadPoolConfig.taskExecutor();
            for(Map<String,Object> map : priNumMap) {
                semaphore.acquire();
                threadPoolTaskExecutor.execute(()->{
                    try {
                        long startDate = System.currentTimeMillis();
                        String priNum = (String) map.get("priNum");
                        //查询这个服刑人员的所有信息
                        String selectOneSql = "select * from middle_old where pri_num = '" + priNum + "' order by state asc";
                        List<Map<String, Object>> maps = null;
                        maps = middleJdbcTemplate.queryForList(selectOneSql);
                        //如果map不为null
                        if (CollectionUtil.isNotEmpty(maps)) {
                            Set<String> set = new HashSet<>(16);
                            for (Map<String, Object> m1 : maps) {
                                String type = (String) m1.get("type");
                                String state = (String) m1.get("state");
                                String statement = (String) m1.get("statement");
                                Date createTime = (Date) m1.get("create_time");
                                Date updateTime = (Date) m1.get("update_time");
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String createDate = sdf.format(createTime);
                                String updateDate = sdf.format(updateTime);
                                Integer failurenum = (Integer) m1.get("failurenum");
                                String reason = (String) m1.get("reason");
                                String createProvinceCode = (String) m1.get("create_province_code");
                                String inProvinceCode = (String) m1.get("in_province_code");
                                Long orderNum = (Long) m1.get("order_num");
                                StringBuilder sb = new StringBuilder();
                                sb.append(statement.toLowerCase());
                                sb.append(createProvinceCode);
                                sb.append(inProvinceCode);
                                if (set.add(sb.toString())) {
                                    String insertSql = "INSERT INTO middle (id,pri_num,type,state,statement,create_time,update_time,failurenum,reason,create_province_code,in_province_code,order_num)" + " VALUES (" + JsonUtil.toText(IdGen.strid()) + "," + JsonUtil.toText(priNum) + "," + JsonUtil.toText(type) + "," + state + "," + JsonUtil.toText(statement) + ",'" + createDate + "','" + updateDate + "'," + JsonUtil.toText(failurenum) + "," + JsonUtil.toText(reason) + ",'" + createProvinceCode + "','" + inProvinceCode + "'," + orderNum + ")";
                                    connectionStatement.addBatch(insertSql);
                                }
                            }
                            String delSql = "delete from middle_old where pri_num = '" + priNum + "'";
                            connection.setAutoCommit(false);
                            connectionStatement.executeBatch();
                            connectionStatement.execute(delSql);
                            connection.commit();
                            long endDate = System.currentTimeMillis();
                            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>插入中间库的数据：插入{}条，删除{}条，编号：{},线程名称:{},耗时:{}s..........................", set.size(),maps.size(),priNum,Thread.currentThread().getName(),(endDate-startDate)/1000);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    } finally {
                        semaphore.release();
                        countDownLatch.countDown();
                        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>剩余罪犯编号数量:{}",countDownLatch.getCount());
                    }
                });
                String priNum = (String) map.get("priNum");
                log.info("循环次数：{},犯罪编号：{}",++i,priNum);
            }
            countDownLatch.await();
        } catch (Exception e) {
            try {
                middleJdbcTemplate.getDataSource().getConnection().rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }

    /**
     * 处理因省份更新时间不一致，跨省数据因数据库结构不一致导致跨省同步出现问题
     * 解决方案：
     * 1、去司法部Middle表查找state=3的本省数据，拿到pri_num
     * 2、组装参数，本地再调用一下同步司法部的方法
     */
   @Scheduled(cron = "${gkzx.azbj.dataSync.cron}")
    public void firstFailureAgainTongbu() {
        log.info("开始执行处理中间库同步失败的sql,去除司法部Middle表查找state=3的本省数据");
        String proCode = CodeUrlUtils.getLocalCode();
        //获取middle数据源
        if (middleJdbcTemplate == null) {
            ApplicationContext applicationContext = BizApplication.applicationContext;
            if (!ObjectUtils.allNotNull(middleJdbcTemplate)) {
                middleJdbcTemplate = (JdbcTemplate) applicationContext.getBean("middleJdbcTemplate");
            }
        }
        //查询middle表中一条执行失败的数据罪犯编号
        String priNumSql = "select pri_num as priNum from middle where state = '3' and in_province_code = '" + proCode + "' limit 1";
        Map<String, Object> priNumMap = middleJdbcTemplate.queryForMap(priNumSql);
        String priNum = (String) priNumMap.get("priNum");
        //查询这个服刑人员的所有执行失败信息，按照时间插入由近到远
        String sql = "select id, pri_num, type,state,statement,create_province_code,in_province_code,failurenum from middle where pri_num = '"+priNum+"' and state = '3' and in_province_code = '" + proCode + "' order by order_num desc";
        //maps存储该服刑人员所有执行失败的信息
        List<Map<String, Object>> maps = null;
        try {
            maps = middleJdbcTemplate.queryForList(sql);
        } catch (DataAccessException e) {
            log.info("司法部中间库Middle扫描：查询结果类型转换异常：{}", e.getMessage());
        }
        //如果maps有值
        if (CollectionUtil.isNotEmpty(maps)) {
            //statements用于存储sql,该服刑人员需要用到的所有信息
            List<Map<String,String>> statements = new ArrayList<>(16);
            String createProvinceCode ="";
            String inProvinceCode = "";
            int failurenum = 0;
            //是否包含delete/update标志
            boolean reverseFlag = false;
            //遍历该服刑人员所有state=3的信息
            for (Map<String, Object> m1 : maps) {
                //取出有用的信息
                String id = (String) m1.get("id");
                String statement = (String) m1.get("statement");
                createProvinceCode = (String) m1.get("create_province_code");
                inProvinceCode = (String) m1.get("in_province_code");
                failurenum = (Integer) m1.get("failurenum");
                failurenum++;
                Map<String,String> temp = new HashMap<>(16);
                //如果sql中包含update或delete，
                if (statement.contains("update ") || statement.contains("delete ")) {
                    reverseFlag = true;
                }
                temp.put("statement", statement);
                temp.put("id", id);
                temp.put("failurenum", failurenum+"");
                statements.add(temp);
            }
            //如果包含delete/update
            if (reverseFlag) {
                // 反转列表
                Collections.reverse(statements);
                //遍历反转后的列表，这个列表是按照时间从远到近排序
                for (Map<String, String> map : statements) {
                    String id = map.get("id");
                    String statement = map.get("statement");
                    int failurenum1 = Integer.parseInt(map.get("failurenum"));
                    String tableName = getTableName(statement);
                    try {
                        if(failurenum1 < 6) {
                            if (StringUtils.isNotBlank(tableName)) {
                                //解析statement(包含insert/update/delete语句)，根据新的数据库结构组装参数
                                if (statement.contains("INSERT ") || statement.contains("insert ")) {
                                    //插入
                                    parseInsert(priNum,statement,tableName,failurenum1,createProvinceCode,inProvinceCode);
                                } else if (statement.contains("update ") || statement.contains("UPDATE ")) {
                                    //更新
                                    parseUpdate(priNum, statement, tableName,failurenum1, createProvinceCode, inProvinceCode);
                                }else {
                                    //删除
                                    parseDelete(priNum, statement, tableName,failurenum1, createProvinceCode, inProvinceCode);
                                }
                                //只能修改需要被替换的那一条
                                String middleSql = "delete from  middle where id = '"+id+"' ";
                                middleJdbcTemplate.update(middleSql);
                                log.info("司法部Middle，state=3重新同步sql成功！");
                            }
                        } else {
                            String middleSql = "update middle set state = '4' where id = '" + id + "'";
                            middleJdbcTemplate.update(middleSql);
                        }
                    } catch (Exception e) {
                        log.info("司法部Middle，state=3重新同步sql失败！原因:{}", e.getMessage());
                    }
                }
            }else {
                Map<String,String> sqlTemp = new HashMap<>();
                for(Map<String,String> temp : statements) {
                    String statement = temp.get("statement");
                    String id = temp.get("id");
                    int failurenum1 = Integer.parseInt(temp.get("failurenum"));
                    int size = sqlTemp.size();
                    noRepeatMap(statement, sqlTemp);
                    //说明有不重复的数据添加进去了
                    if(sqlTemp.size() > size) {
                        if(failurenum < 6) {
                            String tableName = getTableName(statement);
                            if (StringUtils.isNotBlank(tableName)) {
                                //处理middle表中state为3的数据，如果是因为字段问题导致的，重新生成可执行的sql
                                parseInsert(priNum,statement,tableName,failurenum1,createProvinceCode,inProvinceCode);
                                String middleSql = "delete from middle where id = '"+id+"' ";
                                middleJdbcTemplate.update(middleSql);
                            }
                        } else {
                            String middleSql = "update middle set state = '4' where id = '" + id + "'";
                            middleJdbcTemplate.update(middleSql);
                        }
                    }
                }
            }
        } else {
            log.info("司法部Middle表中没有state=3的数据,不需要同步！");
        }
    }


    /**
     * 解析原来的update参数，并根据现在数据库结构创建新的update语句插入到middle表中
     * @param priNum    罪犯编号
     * @param statement 原来的update语句
     * @param tableName 表名
     * @param createProvinceCode    创建sql省份
     * @param inProvinceCode    执行sql省份
     */
    private void parseUpdate(String priNum, String statement, String tableName,int failurenum, String createProvinceCode, String inProvinceCode) {
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>parseUpdate开始执行..................");
        //示例:update prisoner_main set name = a, sex=b, address=c where pri_num ='11111' and id= '1213213'
        //将原来的语句转换为小写
        String s = statement.toLowerCase();
        //s1取的是set之后和where之前的数据：name = a, sex=b, address=c
        String s1 = s.substring(s.indexOf("set")+3,s.indexOf("where"));
        //s2取的是where之后的数据：pri_num ='11111' and id= '1213213'
        String s2 = s.substring(s.indexOf("where")+5);
        //去掉空格
        s1 = s1.trim();
        s2 = s2.trim();
        //update赋值是通过”,“分隔
        String[] ss1 = s1.split(",");
        //update语句的条件是通过and分隔
        String[] ss2 = s2.split("and");
        //param装列名：name,sex,address,pri_num,id
        List<String> param = new ArrayList<>();
        //value装值：a,b,c,1111,1213213
        List<String> value = new ArrayList<>();
        //参数通过逗号分隔后还需要通过=来拆分成对应的param和value
        for (String t1 : ss1) {
            String[] split = t1.split("=");
            param.add(split[0].trim());
            value.add(split[1].trim());
        }
        //条件通过and分隔后还需要通过=来拆分成对应的param和value
        for (String t2 : ss2) {
            String[] split = t2.split("=");
            param.add(split[0].trim());
            value.add(split[1].trim());
        }
        //获取update语句
        String update = JdbcUtils.getAppendSql(tableName, param, value, ss2.length,Constants.Number.NUM_ONE);
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>生成的update语句为:\n${}",update);
        String middleSql = JdbcUtils.createMiddleSql(priNum, update,failurenum, createProvinceCode, inProvinceCode);
        middleJdbcTemplate.update(middleSql);
    }
    /**
     * 解析原来的insert参数，并根据现在数据库结构创建新的insert语句插入到middle表中
     * @param priNum    罪犯编号
     * @param statement 原来的insert语句
     * @param tableName 表名
     * @param createProvinceCode    创建sql省份
     * @param inProvinceCode    执行sql省份
     */
    private void parseInsert(String priNum,String statement,String tableName,int failurenum,String createProvinceCode,String inProvinceCode) {
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>parseInsert开始执行..................");
        statement = statement.toLowerCase();
        String param = statement.substring(statement.indexOf("(") + 1, statement.indexOf(")"));
        String temp = statement.substring(statement.indexOf("values"));
        String value = temp.substring(temp.indexOf("(") + 1, temp.indexOf(")"));
        String[] params = param.split(",");
        String[] values = value.split(",");
        List<String> paramList = Arrays.asList(params);
        List<String> valueList = Arrays.asList(values);
        String insert = JdbcUtils.getAppendSql(tableName, paramList, valueList,Constants.Number.NUM_ZERO,Constants.Number.NUM_TWO);
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>生成的insert语句为:\n${}",insert);
        String middleSql = JdbcUtils.createMiddleSql(priNum, insert,failurenum, createProvinceCode, inProvinceCode);
        middleJdbcTemplate.update(middleSql);
    }

    /**
     * 解析原来的delete参数，并根据现在数据库结构创建新的delete语句插入到middle表中
     * @param priNum    罪犯编号
     * @param statement 原来的insert语句
     * @param tableName 表名
     * @param createProvinceCode    创建sql省份
     * @param inProvinceCode    执行sql省份
     */
    private void parseDelete(String priNum, String statement, String tableName, int failurenum,String createProvinceCode, String inProvinceCode) {
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>parseDelete开始执行..................");
        String s = statement.toLowerCase();
        String s1 = s.substring(s.indexOf("where")+5);
        s1 = s1.trim();
        String[] ss1 = s1.split("and");
        List<String> param = new ArrayList<>();
        List<String> value = new ArrayList<>();
        for (String t1 : ss1) {
            String[] split = t1.split("=");
            param.add(split[0]);
            value.add(split[1]);
        }
        String delete = JdbcUtils.getAppendSql(tableName, param, value, Constants.Number.NUM_ZERO, Constants.Number.NUM_THREE);
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>生成的delete语句为:\n${}",delete);
        String middleSql = JdbcUtils.createMiddleSql(priNum, delete,failurenum, createProvinceCode, inProvinceCode);
        middleJdbcTemplate.update(middleSql);
    }

    /**
     * 根据原来的Sql语句判断要操作的表名
     * @param statement 原来的sql语句
     * @return 要操作的表名称
     */
    private String getTableName(String statement) {
        String tableName = "";
        if (statement.contains("prisoner_main")) {
            tableName = "prisoner_main";
        } else if (statement.contains("prisoner_fx")) {
            tableName = "prisoner_fx";
        } else if (statement.contains("prisave_temp")) {
            tableName = "prisave_temp";
        } else if (statement.contains("prisoner_hc")) {
            tableName = "prisoner_hc";
        } else if (statement.contains("prisoner_sr")) {
            tableName = "prisoner_sr";
        } else if (statement.contains("prisoner_zn")) {
            tableName = "prisoner_zn";
        } else if (statement.contains("prisoner_zz")) {
            tableName = "prisoner_zz";
        } else if (statement.contains("xj_receipt")) {
            tableName = "xj_receipt";
        } else if (statement.contains("xj_receipt_data")) {
            tableName = "xj_receipt_data";
        } else if (statement.contains("upload_appendix_file")) {
            tableName = "upload_appendix_file";
        } else if (statement.contains("prisoner_cjpg")) {
            tableName = "prisoner_cjpg";
        } else if (statement.contains("prisoner_xjxx")) {
            tableName = "prisoner_xjxx";
        } else if (statement.contains("prisoners_jxjs")) {
            tableName = "prisoners_jxjs";
        } else if (statement.contains("ab_prisoners")) {
            tableName = "ab_prisoners";
        } else if (statement.contains("ab_xiaozu")) {
            tableName = "ab_xiaozu";
        } else if (statement.contains("ab_anzhi")) {
            tableName = "ab_anzhi";
        } else if (statement.contains("ab_bangfu")) {
            tableName = "ab_bangfu";
        } else if (statement.contains("ab_lieguan")) {
            tableName = "ab_lieguan";
        }
        return tableName;
    }

    /**
     * 一个人执行失败的跨省语句，如果没有delete和update语句的话，只保留对每张表操作的最新的一条
     * @param statement 原来的sql语句
     * @param sqlTemp   操作表最新的sql
     */
    private void noRepeatMap(String statement, Map<String, String> sqlTemp) {
        if (statement.contains("prisoner_main")) {
            sqlTemp.putIfAbsent("prisoner_main", statement);
        } else if (statement.contains("prisoner_fx")) {
            sqlTemp.putIfAbsent("prisoner_fx", statement);
        } else if (statement.contains("prisave_temp")) {
            sqlTemp.putIfAbsent("prisave_temp", statement);
        } else if (statement.contains("prisoner_hc")) {
            sqlTemp.putIfAbsent("prisoner_hc", statement);
        } else if (statement.contains("prisoner_sr")) {
            sqlTemp.putIfAbsent("prisoner_sr", statement);
        } else if (statement.contains("prisoner_zn")) {
            sqlTemp.putIfAbsent("prisoner_zn", statement);
        } else if (statement.contains("prisoner_zz")) {
            sqlTemp.putIfAbsent("prisoner_zz", statement);
        } else if (statement.contains("xj_receipt")) {
            sqlTemp.putIfAbsent("xj_receipt", statement);
        } else if (statement.contains("xj_receipt_data")) {
            sqlTemp.putIfAbsent("xj_receipt_data", statement);
        } else if (statement.contains("upload_appendix_file")) {
            sqlTemp.putIfAbsent("upload_appendix_file", statement);
        } else if (statement.contains("prisoner_cjpg")) {
            sqlTemp.putIfAbsent("prisoner_cjpg", statement);
        } else if (statement.contains("prisoner_xjxx")) {
            sqlTemp.putIfAbsent("prisoner_xjxx", statement);
        } else if (statement.contains("prisoners_jxjs")) {
            sqlTemp.putIfAbsent("prisoners_jxjs", statement);
        } else if (statement.contains("ab_prisoners")) {
            sqlTemp.putIfAbsent("ab_prisoners", statement);
        } else if (statement.contains("ab_xiaozu")) {
            sqlTemp.putIfAbsent("ab_xiaozu", statement);
        } else if (statement.contains("ab_anzhi")) {
            sqlTemp.putIfAbsent("ab_anzhi", statement);
        } else if (statement.contains("ab_bangfu")) {
            sqlTemp.putIfAbsent("ab_bangfu", statement);
        } else if (statement.contains("ab_lieguan")) {
            sqlTemp.putIfAbsent("ab_lieguan", statement);
        }
    }

    /**
     * 处理middle表中state为3的数据，如果是因为字段问题导致的，重新生成可执行的sql
     * @param priNum    罪犯编号
     * @param tableName 操作的表名
     * @param createProvinceCode    发起sql的省份
     * @param inProvinceCode    要执行sql的省份
     * @param middleJdbcTemplate    中间库数据源
     */
    private void handleErrorSql(String priNum,String tableName, int failurenum,String createProvinceCode,String inProvinceCode,JdbcTemplate middleJdbcTemplate) {
        List<String> newSql;
        newSql = JdbcUtils.getInsertSql(tableName,"pri_num",priNum,null,null,null,null);
        //如果newSql中有值
        if (!Constants.Number.NUM_ZERO.equals(newSql.size())) {
            try {
                String middleSql = JdbcUtils.createMiddleSql(priNum, newSql.get(0), failurenum,createProvinceCode, inProvinceCode);
                middleJdbcTemplate.execute(middleSql);
            } catch (DataAccessException e) {
                throw new RuntimeException(e);
            }
        }
    }
}